package com.dx.financehy.utils.excel;

import cn.hutool.core.util.StrUtil;
import com.dx.financehy.exception.BadRequestException;
import org.apache.poi.hssf.usermodel.DVConstraint;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.*;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.web.multipart.MultipartFile;

import javax.annotation.PostConstruct;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;

/**
 * excel模板生成工具类
 */
public class ExcelTemplateUtils {

    private static ExcelTemplateUtils templateUtils;

    @Value("${spring.profiles.active}")
    private static String env;

    @PostConstruct
    public void init() {
        templateUtils = this;
    }

    public static void checkFile(MultipartFile file) {
        List<String> filenames = StrUtil.split(file.getOriginalFilename(), ".");
        if (filenames.size() < 2) {
            throw new BadRequestException("请上传正确文件！");
        }
        if (!StrUtil.equalsAnyIgnoreCase(filenames.get(1), "xlsx", "xls")) {
            throw new BadRequestException("上传的文件类型不正确，只支持xls、xlsx文件！");
        }
    }

    public static Workbook getWorkbook(List<String> titles, Map<String, List<String>> dictMap, Map<String, List<String>> dictSonMap, Map<String, List<String>> dictGrandMap, Map<String, String> parentSonMap, Map<String, Integer> formulaIndex, Set<Integer> numberFormulaIndex, Set<Integer> numberWithDecimalsFormulaIndex, Set<Integer> numberWithTwoDecimalsFormulaIndex) {
        ExcelTemplateBean bean = buildTemplateBean(titles, dictMap, dictSonMap, dictGrandMap, parentSonMap, formulaIndex, numberFormulaIndex, numberWithDecimalsFormulaIndex, numberWithTwoDecimalsFormulaIndex);
        return getSXSSFWorkbookTemplate(bean);
    }

    public static Workbook getWorkbook1(List<String> titles, Map<String, List<String>> dictMap, Map<String, List<String>> dictSonMap, Map<String, List<String>> dictGrandMap, Map<String, String> parentSonMap, Map<String, Integer> formulaIndex, Set<Integer> numberFormulaIndex, Set<Integer> numberWithDecimalsFormulaIndex, Set<Integer> numberWithTwoDecimalsFormulaIndex) {
        ExcelTemplateBean bean = buildTemplateBean(titles, dictMap, dictSonMap, dictGrandMap, parentSonMap, formulaIndex, numberFormulaIndex, numberWithDecimalsFormulaIndex, numberWithTwoDecimalsFormulaIndex);
        return getSXSSFWorkbookTemplate1(bean);
    }

    /**
     * @param titles                            构建导入模板标题行
     * @param dictMap                           构建导入模板下拉列表字典
     * @param dictSonMap                        子集字典数据集合
     * @param parentSonMap                      级联下拉选
     * @param formulaIndex                      构建数据有效性公式-下标集合
     * @param numberFormulaIndex                数字格式有效性(整数)
     * @param numberWithDecimalsFormulaIndex    数字格式有效性(带小数1位)
     * @param numberWithTwoDecimalsFormulaIndex 数字格式有效性(带小数2位)
     * @return
     */
    private static ExcelTemplateBean buildTemplateBean(List<String> titles, Map<String, List<String>> dictMap, Map<String, List<String>> dictSonMap, Map<String, List<String>> dictGrandMap, Map<String, String> parentSonMap, Map<String, Integer> formulaIndex, Set<Integer> numberFormulaIndex, Set<Integer> numberWithDecimalsFormulaIndex, Set<Integer> numberWithTwoDecimalsFormulaIndex) {
        ExcelTemplateBean bean = new ExcelTemplateBean();
//        formulaIndex.put("药品类型", 0);

        //指定哪些列需要做数值格式有效性验证
//        numberFormulaIndex.add(14);

        //指定哪些列需要做数值格式有效性验证(允许带2位小数)
//        numberWithTwoDecimalsFormulaIndex.add(11);


        bean.setTitles(titles);
        bean.setDictMap(dictMap);
        bean.setDictSonMap(dictSonMap);
        bean.setDictGrandMap(dictGrandMap);
        bean.setParentSonMap(parentSonMap);
        bean.setFormulaIndex(formulaIndex);
        bean.setNumberFormulaIndex(numberFormulaIndex);
        bean.setNumberWithDecimalsFormulaIndex(numberWithDecimalsFormulaIndex);
        bean.setNumberWithTwoDecimalsFormulaIndex(numberWithTwoDecimalsFormulaIndex);
        return bean;
    }

    public static Workbook getSXSSFWorkbookTemplate(ExcelTemplateBean excel) {
        // 创建一个excel
        Workbook book = new XSSFWorkbook();

        /**
         * 写表头
         */
        // 创建需要用户填写的sheet
        List<String> titles = excel.getTitles();
        XSSFSheet dataSheet = (XSSFSheet) book.createSheet("信息表");
        // 数据表，标题写入第一行
        Row titlesRow = dataSheet.createRow(0);
        for (int i = 0; i < titles.size(); i++) {
            Cell cell = titlesRow.createCell(i);
            dataSheet.setColumnWidth(i, 5000);
            //行放入值
            String title = titles.get(i);
            cell.setCellValue(title);
        }

        Map<String, String> parentSonMap = excel.getParentSonMap();

        Map<String, List<String>> dictMap = excel.getDictMap();

        Map<String, List<String>> dictSonMap = excel.getDictSonMap();

        Map<String, List<String>> dictGrandMap = excel.getDictGrandMap();

        /**
         * 创建一个专门用来存放地区信息的隐藏sheet页，因此也不能在现实页之前创建，否则无法隐藏。
         */
        Sheet hideSheet = book.createSheet(excel.getDictSheetName());
        //这一行作用是将此sheet隐藏，功能未完成时注释此行,可以查看隐藏sheet中信息是否正确
        if ("prod".equals(env)){
            book.setSheetHidden(book.getSheetIndex(hideSheet), true);
        }

        //添加所有字典
        Map<String, List<String>> dictAllMap = new HashMap<>();
        dictAllMap.putAll(dictMap);
        dictAllMap.putAll(dictSonMap);
        dictAllMap.putAll(dictGrandMap);

        //先写父级字典
        int rowId = 0;

        Set<String> keyAllMap = dictAllMap.keySet();
        if (!dictAllMap.isEmpty()) {
            for (String key : keyAllMap) {
                Row parent = hideSheet.createRow(rowId++);
                parent.createCell(0).setCellValue(key);
                List<String> list = dictAllMap.get(key);
                for (int i = 0; i < list.size(); i++) {
                    Cell provinceCell = parent.createCell(i + 1);
                    provinceCell.setCellValue(list.get(i));
                }

                // 添加名称管理器
                String range = getRange(1, rowId, list.size());
                Name name = book.createName();
                //key不可重复
                name.setNameName(key);
                String formula = excel.getDictSheetName() + "!" + range;
                name.setRefersToFormula(formula);
            }
        }

        /**
         * 操作父类下拉选
         */
        //存父类的行数
        if (!dictMap.isEmpty()) {
            Set<String> fatherKey = dictMap.keySet();
            for (String key : fatherKey) {
                //循环写入父级下拉选
                XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper((XSSFSheet) dataSheet);
                DataValidationConstraint fatherConstraint = dvHelper.createFormulaListConstraint(key);
                //获取作用列
                int i = titles.indexOf(key);
                // 四个参数分别是：起始行、终止行、起始列、终止列
                CellRangeAddressList fatherRange = new CellRangeAddressList(1, excel.getValidColumn(), i, i);
                DataValidation fatherDataValidation = dvHelper.createValidation(fatherConstraint, fatherRange);
                //验证
                fatherDataValidation.createErrorBox("error", "请选择正确的值");
                fatherDataValidation.setShowErrorBox(true);
                fatherDataValidation.setSuppressDropDownArrow(true);
                dataSheet.addValidationData(fatherDataValidation);
            }
        }

        /**
         * 操作子类类下拉选
         */
        if (!parentSonMap.isEmpty()) {
            Set<String> keyParentMap = parentSonMap.keySet();
            for (String key : keyParentMap) {
                //对前1000行设置有效性
                for (int i = 2; i < excel.getValidColumn(); i++) {
                    String s = parentSonMap.get(key);
                    String fatherIndex = s.split(":")[0];
                    String sonIndex = s.split(":")[1];
                    setDataValidation(numToLetter(fatherIndex).toUpperCase(), dataSheet, i, Integer.valueOf(sonIndex));
                }
            }
        }

        // 数据验证助手
        DataValidationHelper dvHelper = dataSheet.getDataValidationHelper();

        // 下拉列表验证并引用隐藏sheet页的字典信息
        Map<String, Integer> formulaIndex = excel.getFormulaIndex();
        for (Map.Entry<String, Integer> entry : formulaIndex.entrySet()) {
            dataSheet.addValidationData(getDataValidation(entry.getKey(), dvHelper, entry.getValue()));
        }

        // 数字格式验证(整数)
        for (Integer index : excel.getNumberFormulaIndex()) {
            dataSheet.addValidationData(getNumericDataValidation(dvHelper, index - 1, "数值必须是大于0的整数,范围(1~99999)！", "99999"));
        }

        // 数字格式验证(允许带1位小数)
        for (Integer index : excel.getNumberWithDecimalsFormulaIndex()) {
            dataSheet.addValidationData(getNumericDataValidation2(dvHelper, index - 1, "输入必须是大于0的数,允许1位小数,最大99999！", "0.1", "99999"));

        }

        // 数字格式验证(允许带2位小数)
        for (Integer index : excel.getNumberWithTwoDecimalsFormulaIndex()) {
            dataSheet.addValidationData(getNumericDataValidation2(dvHelper, index - 1, "输入必须是大于等于0的数,允许2位小数,最大99999！", "0", "99999"));
        }
        return book;
    }

    public static Workbook getSXSSFWorkbookTemplate1(ExcelTemplateBean excel) {
        // 创建一个excel
        Workbook book = new XSSFWorkbook();

        /**
         * 写表头
         */
        // 创建需要用户填写的sheet
        List<String> titles = excel.getTitles();
        XSSFSheet dataSheet = (XSSFSheet) book.createSheet("信息表");
        // 数据表，标题写入第一行
        Row titlesRow = dataSheet.createRow(0);
        for (int i = 0; i < titles.size(); i++) {
            Cell cell = titlesRow.createCell(i);
            dataSheet.setColumnWidth(i, 5000);
            //行放入值
            String title = titles.get(i);
            cell.setCellValue(title);
        }

        Map<String, String> parentSonMap = excel.getParentSonMap();

        Map<String, List<String>> dictMap = excel.getDictMap();

        Map<String, List<String>> dictSonMap = excel.getDictSonMap();

        Map<String, List<String>> dictGrandMap = excel.getDictGrandMap();

        /**
         * 创建一个专门用来存放地区信息的隐藏sheet页，因此也不能在现实页之前创建，否则无法隐藏。
         */
        Sheet hideSheet = book.createSheet(excel.getDictSheetName());
        //这一行作用是将此sheet隐藏，功能未完成时注释此行,可以查看隐藏sheet中信息是否正确
        book.setSheetHidden(book.getSheetIndex(hideSheet), true);

        //添加所有字典
        Map<String, List<String>> dictAllMap = new HashMap<>();
        dictAllMap.putAll(dictMap);
        dictAllMap.putAll(dictSonMap);
        dictAllMap.putAll(dictGrandMap);

        //先写父级字典
        int rowId = 0;

        Set<String> keyAllMap = dictAllMap.keySet();
        if (!dictAllMap.isEmpty()) {
            for (String key : keyAllMap) {
                Row parent = hideSheet.createRow(rowId++);
                parent.createCell(0).setCellValue(key);
                List<String> list = dictAllMap.get(key);
                for (int i = 0; i < list.size(); i++) {
                    Cell provinceCell = parent.createCell(i + 1);
                    provinceCell.setCellValue(list.get(i));
                }

                // 添加名称管理器
                String range = getRange(1, rowId, list.size());
                Name name = book.createName();
                //key不可重复
                name.setNameName(key);
                String formula = excel.getDictSheetName() + "!" + range;
                name.setRefersToFormula(formula);
            }
        }

        /**
         * 操作父类下拉选
         */
        //存父类的行数
        if (!dictMap.isEmpty()) {
            Set<String> fatherKey = dictMap.keySet();
            for (String key : fatherKey) {
                //循环写入父级下拉选
                XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper((XSSFSheet) dataSheet);
                DataValidationConstraint fatherConstraint = dvHelper.createFormulaListConstraint(key);
                //获取作用列
                int i = titles.indexOf(key);
                // 四个参数分别是：起始行、终止行、起始列、终止列
                CellRangeAddressList fatherRange = new CellRangeAddressList(1, excel.getValidColumn(), i, i);
                DataValidation fatherDataValidation = dvHelper.createValidation(fatherConstraint, fatherRange);
                //验证
                fatherDataValidation.createErrorBox("error", "请选择正确的值");
                fatherDataValidation.setShowErrorBox(true);
                fatherDataValidation.setSuppressDropDownArrow(true);
                dataSheet.addValidationData(fatherDataValidation);
            }
        }

        /**
         * 操作子类类下拉选
         */
        if (!parentSonMap.isEmpty()) {
            Set<String> keyParentMap = parentSonMap.keySet();
            for (String key : keyParentMap) {
                //对前1000行设置有效性
                for (int i = 2; i < excel.getValidColumn(); i++) {
                    String s = parentSonMap.get(key);
                    String fatherIndex = s.split(":")[0];
                    String sonIndex = s.split(":")[1];
                    setDataValidation(numToLetter(fatherIndex).toUpperCase(), dataSheet, i, Integer.valueOf(sonIndex));
                }
            }
        }

        // 数据验证助手
        DataValidationHelper dvHelper = dataSheet.getDataValidationHelper();

        // 下拉列表验证并引用隐藏sheet页的字典信息
        Map<String, Integer> formulaIndex = excel.getFormulaIndex();
        for (Map.Entry<String, Integer> entry : formulaIndex.entrySet()) {
            dataSheet.addValidationData(getDataValidation(entry.getKey(), dvHelper, entry.getValue()));
        }

        // 数字格式验证(整数)
        for (Integer index : excel.getNumberFormulaIndex()) {
            dataSheet.addValidationData(getNumericDataValidation(dvHelper, index - 1, "数值必须是大于0的整数,范围(1~99999)！", "99999"));
        }

        // 数字格式验证(允许带1位小数)
        for (Integer index : excel.getNumberWithDecimalsFormulaIndex()) {
            dataSheet.addValidationData(getNumericDataValidation2(dvHelper, index - 1, "输入必须是大于0的数,允许1位小数,最大99999！", "0.1", "99999"));

        }

        // 数字格式验证(允许带2位小数)
        for (Integer index : excel.getNumberWithTwoDecimalsFormulaIndex()) {
            dataSheet.addValidationData(getNumericDataValidation2(dvHelper, index - 1, "输入必须是大于等于-9999的数,允许2位小数,最大99999！", "-9999", "99999"));
        }
        return book;
    }

    /**
     * 计算formula(用于下拉框引用的字典范围)
     *
     * @param offset   偏移量，如果给0，表示从A列开始，1，就是从B列
     * @param rowId    第几行
     * @param colCount 一共多少列
     * @return 如果给入参 1,1,10. 表示从B1-K1。最终返回 $B$1:$K$1
     */
    public static String getRange(int offset, int rowId, int colCount) {
        if (colCount <= 0) {
            colCount = 1;
        }
        char start = (char) ('A' + offset);
        if (colCount <= 25) {
            char end = (char) (start + colCount - 1);
            return "$" + start + "$" + rowId + ":$" + end + "$" + rowId;
        } else {
            char endPrefix = 'A';
            char endSuffix = 'A';
            // 26-51之间，包括边界（仅两次字母表计算）
            if ((colCount - 25) / 26 == 0 || colCount == 51) {
                if ((colCount - 25) % 26 == 0) {
                    // 边界值
                    endSuffix = (char) ('A' + 25);
                } else {
                    endSuffix = (char) ('A' + (colCount - 25) % 26 - 1);
                }
            } else if (colCount > 51 && colCount <= 701) {
                // 51以上
                if ((colCount - 25) % 26 == 0) {
                    endSuffix = (char) ('A' + 25);
                    endPrefix = (char) (endPrefix + (colCount - 25) / 26 - 1);
                } else {
                    endSuffix = (char) ('A' + (colCount - 25) % 26 - 1);
                    endPrefix = (char) (endPrefix + (colCount - 25) / 26);
                }
            } else {
                if (colCount > 16383) {
                    colCount = 16383;
                }
                char left = 'A';
                char centre = 'A';
                char right = 'A';
                if (colCount / 26 % 26 == 0) {
                    // 边界值
                    centre = (char) ('A' + 25);
                    left = (char) (left + (colCount / 26 / 26 % 26 - 2));
                } else {
                    left = (char) (left + (colCount / 26 / 26 % 26 - 1));
                    centre = (char) (centre + (colCount / 26 % 26 - 1));
                }

                right = (char) (right + colCount % 26);
                return "$" + start + "$" + rowId + ":$" + left + centre + right + "$" + rowId;
            }
            return "$" + start + "$" + rowId + ":$" + endPrefix + endSuffix + "$" + rowId;
        }
    }


    /**
     * 设置有效性，从名称管理器查询下拉列表集合
     *
     * @param formula   有效性：公式(名称管理器/左侧单元格)
     * @param dvHelper  数据约束辅助类
     * @param collIndex 限制列所在的下标
     */
    public static DataValidation getDataValidation(String formula, DataValidationHelper dvHelper, Integer collIndex) {
        // 数据验证约束
        // 公式
        DataValidationConstraint dvConstraint = dvHelper.createFormulaListConstraint(formula);
        // 单元格，适用范围
        CellRangeAddressList regions = new CellRangeAddressList(1, 65535, collIndex, collIndex);
        // 创建验证
        DataValidation dataValidation = dvHelper.createValidation(dvConstraint, regions);

        // 设置单元允许为空(不为null的话，输错了就从单元格出不来了)
        dataValidation.setEmptyCellAllowed(true);
        // 设置下拉箭头
        dataValidation.setSuppressDropDownArrow(true);
        // 设置显示错误框;
        dataValidation.setShowErrorBox(true);
        // 设置显示下拉框提示(一直有提示，很烦人的)
        // dataValidation.setShowPromptBox(true);
        // 设置输入错误提示信息
        //dataValidation.createPromptBox("下拉选择提示", "请使用下拉方式选择合适的值！");
        dataValidation.createErrorBox("下拉选择提示", "请使用下拉方式选择合适的值！");

        // 添加验证到表单
        return dataValidation;
    }

    /**
     * 设置时间格式有效性，默认1970-01-01 到 9999-12-31
     * 默认格式："yyyy-MM-dd"
     *
     * @param excel
     * @param dvHelper
     * @param collIndex
     * @return
     */
    public static DataValidation getDateDataValidation(ExcelTemplateBean excel, DataValidationHelper dvHelper, Integer collIndex) {
        // 事件格式验证
        DataValidationConstraint dvConstraint = dvHelper.createDateConstraint(DVConstraint.ValidationType.DATE, excel.getStartDate(), excel.getEndDate(), "yyyy-MM-dd");
        // 单元格，适用范围
        CellRangeAddressList regions = new CellRangeAddressList(0, -1, 8, 8);
        // 创建验证
        DataValidation dataValidation = dvHelper.createValidation(dvConstraint, regions);
        /*设置单元允许为空*/
        dataValidation.setEmptyCellAllowed(true);
        /* 设置显示错误框;*/
        dataValidation.setShowErrorBox(true);
        dataValidation.createErrorBox("格式错误", "请输入正确的日期格式！");
        // 添加验证到表单
        return dataValidation;
    }

    /**
     * 设置数字大小有效性，设置单元格数值只能在1-99999之间
     *
     * @param dvHelper
     * @param collIndex
     * @return
     */
    public static DataValidation getNumericDataValidation(DataValidationHelper dvHelper, Integer collIndex, String erroMsg, String maxNumber) {
        // 事件格式验证
        DataValidationConstraint dvConstraint = dvHelper.createNumericConstraint(DVConstraint.ValidationType.INTEGER, DVConstraint.OperatorType.BETWEEN, "1", maxNumber);
        // 单元格，适用范围
        CellRangeAddressList regions = new CellRangeAddressList(1, 65535, collIndex, collIndex);
        // 创建验证
        DataValidation dataValidation = dvHelper.createValidation(dvConstraint, regions);
        /*设置单元允许为空*/
        dataValidation.setEmptyCellAllowed(true);
        /* 设置显示错误框;*/
        dataValidation.setShowErrorBox(true);
        dataValidation.createErrorBox("格式错误", erroMsg);
        // 添加验证到表单
        return dataValidation;
    }


    /**
     * 设置数字大小有效性，设置单元格数值只能在1-99999之间(允许小数)
     *
     * @param dvHelper
     * @param collIndex
     * @return
     */
    public static DataValidation getNumericDataValidation2(DataValidationHelper dvHelper, Integer collIndex, String erroMsg, String minNumber, String maxNumber) {
        // 事件格式验证
        DataValidationConstraint dvConstraint = dvHelper.createNumericConstraint(DVConstraint.ValidationType.DECIMAL, DVConstraint.OperatorType.BETWEEN, minNumber, maxNumber);
        // 单元格，适用范围
        CellRangeAddressList regions = new CellRangeAddressList(1, 65535, collIndex, collIndex);
        // 创建验证
        DataValidation dataValidation = dvHelper.createValidation(dvConstraint, regions);
        /*设置单元允许为空*/
        dataValidation.setEmptyCellAllowed(true);
        /* 设置显示错误框;*/
        dataValidation.setShowErrorBox(true);
        dataValidation.createErrorBox("格式错误", erroMsg);
        // 添加验证到表单
        return dataValidation;
    }

    /**
     * 设置标题行样式
     *
     * @param book
     * @return
     */
    public static CellStyle getCellStyle(SXSSFWorkbook book, DataFormat dataFormat, boolean lock, String forMat) {
        CellStyle numberStyle = book.createCellStyle();
        numberStyle.setAlignment(HorizontalAlignment.CENTER);
        numberStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        numberStyle.setLocked(lock);
        // 设置单元格文本样式为指定样式
        numberStyle.setDataFormat(dataFormat.getFormat(forMat));
        return numberStyle;
    }

    // 将字母转换成数字
    public static int letterToNum(String input) {
        for (byte b : input.getBytes()) {
            return b - 96;
        }
        return 0;
    }

    // 将数字转换成字母
    public static String numToLetter(String input) {
        for (byte b : input.getBytes()) {
            return String.valueOf(((char) (b + 48)));
        }
        return "";
    }

    /**
     * 设置有效性
     *
     * @param offset 父类字典列
     * @param sheet
     * @param rowNum 作用行数
     * @param colNum 子类字典列
     */
    public static void setDataValidation(String offset, XSSFSheet sheet, int rowNum, int colNum) {
        XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper(sheet);
        DataValidation data_validation_list;
        data_validation_list = getDataValidationByFormula("INDIRECT($" + offset + (rowNum) + ")", rowNum, colNum, dvHelper);
        sheet.addValidationData(data_validation_list);
    }

    /**
     * 加载下拉列表内容
     *
     * @param formulaString
     * @param naturalRowIndex
     * @param naturalColumnIndex
     * @param dvHelper
     * @return
     */
    private static DataValidation getDataValidationByFormula(String formulaString, int naturalRowIndex, int naturalColumnIndex, XSSFDataValidationHelper dvHelper) {
        // 加载下拉列表内容
        // 举例：若formulaString = "INDIRECT($A$2)" 表示规则数据会从名称管理器中获取key与单元格 A2 值相同的数据，
        //如果A2是江苏省，那么此处就是江苏省下的市信息。
        XSSFDataValidationConstraint dvConstraint = (XSSFDataValidationConstraint) dvHelper.createFormulaListConstraint(formulaString);
        // 设置数据有效性加载在哪个单元格上。
        // 四个参数分别是：起始行、终止行、起始列、终止列
        int firstRow = naturalRowIndex - 1;
        int lastRow = naturalRowIndex - 1;
        int firstCol = naturalColumnIndex - 1;
        int lastCol = naturalColumnIndex - 1;
        CellRangeAddressList regions = new CellRangeAddressList(firstRow,
                lastRow, firstCol, lastCol);
        // 数据有效性对象
        // 绑定
        XSSFDataValidation data_validation_list = (XSSFDataValidation) dvHelper.createValidation(dvConstraint, regions);
        data_validation_list.setEmptyCellAllowed(false);
        if (data_validation_list instanceof XSSFDataValidation) {
            data_validation_list.setSuppressDropDownArrow(true);
            data_validation_list.setShowErrorBox(true);
        } else {
            data_validation_list.setSuppressDropDownArrow(false);
        }
        // 设置输入信息提示信息
        data_validation_list.createPromptBox("下拉选择提示", "请使用下拉方式选择合适的值！");
        // 设置输入错误提示信息
        //data_validation_list.createErrorBox("选择错误提示", "你输入的值未在备选列表中，请下拉选择合适的值！");
        return data_validation_list;
    }
}
